package com.alibaba.polardbx.qatest.ddl.cdc.entity;

/**
 * description:
 * author: ziyang.lb
 * create: 2023-12-12 17:30
 **/
public interface PartitionTableSqls {

    String CREATE_RANGE_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` int NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL\n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  \n"
        + "PARTITION BY RANGE(YEAR(a))\n"
        + "(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,\n"
        + " PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,\n"
        + " PARTITION p2 VALUES LESS THAN (2010) ENGINE = InnoDB,\n"
        + " PARTITION p3 VALUES LESS THAN (2020) ENGINE = InnoDB)";

    String CREATE_RANGE_COLUMN_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  \n"
        + "PARTITION BY RANGE COLUMNS(a)\n"
        + "(PARTITION p0 VALUES LESS THAN ('1991-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p1 VALUES LESS THAN ('2001-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p2 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p3 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p4 VALUES LESS THAN ('2031-01-01') ENGINE = InnoDB);";

    String CREATE_HASH_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` int NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL\n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  \n"
        + "PARTITION BY HASH(TO_DAYS(a)) PARTITIONS 8";

    String CREATE_HASH_KEY_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  \n"
        + "PARTITION BY KEY(e,id) PARTITIONS 8";

    String CREATE_LIST_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 \n"
        + "PARTITION BY LIST(YEAR(a))\n"
        + "(PARTITION p0 VALUES IN (1990,1991,1992) ENGINE = InnoDB,\n"
        + " PARTITION p1 VALUES IN (1800,1801,1802) ENGINE = InnoDB,\n"
        + " PARTITION p2 VALUES IN (2010,2012,2013) ENGINE = InnoDB,\n"
        + " PARTITION p3 VALUES IN (2020,2022) ENGINE = InnoDB,\n"
        + " PARTITION p4 VALUES IN (2050,2052) ENGINE = InnoDB)";

    String CREATE_LIST_COLUMN_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  \n"
        + "PARTITION BY LIST COLUMNS(a)\n"
        + "(PARTITION p0 VALUES IN ('1990-01-01','1991-01-01','1992-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p1 VALUES IN ('1800-01-01','1801-01-01','1802-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p2 VALUES IN ('2010-01-01','2012-01-01','2013-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p3 VALUES IN ('2020-01-01','2022-01-01') ENGINE = InnoDB,\n"
        + " PARTITION p4 VALUES IN ('2050-01-01','2052-01-01') ENGINE = InnoDB)";

    String CREATE_BROADCAST_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 broadcast \n";

    String CREATE_SINGLE_TABLE_SQL = "CREATE TABLE `%s` (\n"
        + "     `id` bigint not null,\n"
        + "     `a` datetime NOT NULL,\n"
        + "     `b` datetime NOT NULL,\n"
        + "     `c` varchar(20) NOT NULL,\n"
        + "     `d` datetime NOT NULL,\n"
        + "     `e` int NOT NULL,\n"
        + "     `f` bigint NOT NULL,\n"
        + "     `g` bigint NOT NULL,\n"
        + "     `h` bigint NOT NULL,\n"
        + "     `i` bigint NOT NULL,\n"
        + "     PRIMARY KEY (`id`) \n"
        + ") single \n";

    String REORG_TABLE_GROUP_PREFIX = "cdc_reorg_list_tg";
    String CREATE_REORG_TABLE_GROUP = "create tablegroup if not exists %s";
    String CREATE_REORG_TABLE_1 =
        "create table sp_list_list_1 (id int not null primary key, age int, name char(32), ctime datetime, remark varchar(32))\n"
            + "partition by list (id)\n"
            + "subpartition by list (age) subpartition template (\n"
            + "subpartition sp1 values in (10),\n"
            + "subpartition sp2 values in (20),\n"
            + "subpartition sp3 values in (30),\n"
            + "subpartition sp4 values in (40),\n"
            + "subpartition sp5 values in (50)\n"
            + ") (\n"
            + "partition p1 values in (1000),\n"
            + "partition p2 values in (2000),\n"
            + "partition p3 values in (3000),\n"
            + "partition p4 values in (4000),\n"
            + "partition p5 values in (5000)\n"
            + ") tablegroup=%s";
    String CREATE_REORG_TABLE_2 =
        "create table sp_list_list_2 (id int not null primary key, age int, name char(32), ctime datetime, remark varchar(32))\n"
            + "partition by list (id)\n"
            + "subpartition by list (age) subpartition template (\n"
            + "subpartition sp1 values in (10),\n"
            + "subpartition sp2 values in (20),\n"
            + "subpartition sp3 values in (30),\n"
            + "subpartition sp4 values in (40),\n"
            + "subpartition sp5 values in (50)\n"
            + ") (\n"
            + "partition p1 values in (1000),\n"
            + "partition p2 values in (2000),\n"
            + "partition p3 values in (3000),\n"
            + "partition p4 values in (4000),\n"
            + "partition p5 values in (5000)\n"
            + ") tablegroup=%s";
    String REORG_TABLE_GROUP = "alter tablegroup %s reorganize partition p1,p2,p3,p4,p5 into (\n"
        + "partition p1 values in (1000,2000),\n"
        + "partition p2 values in (3000,4000,5000)\n"
        + ")";
}
